package com.rjj.wlqq.tool.excel.html;

import com.rjj.wlqq.tool.io.FileKit;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;

/**
 * 解析html中的table并且转换为excel导出
 * 只可以解析一个table,嵌套table,th,tr,td就会出问题
 * <p>
 * <p>
 * 表格数据格式
 * 主要格式
 *   <table>
 *       <tr><th></th><th></th><th></th></tr>
 *       <tr><td></td><td></td><td></td></tr>
 *       <tr><td></td><td></td><td></td></tr>
 *       <tr><td></td><td></td><td></td></tr>
 *   </table>
 * <p>
 * 例子：
 *   String s = "<html>\n" +
 *                 "<head>\n" +
 *                 "<meta http-equiv=Content-Type content=\"text/html; charset=UTF-8\">\n" +
 *                 "<meta name=ProgId content=Excel.Sheet>\n" +
 *                 "<meta name=Generator content=\"Microsoft Excel 15\">\n" +
 *                 "</head>\n" +
 *                 "<body>\n" +
 *                 "<table>\n" +
 *                 "\t<thead>\n" +
 *                 "\t\t<tr>\n" +
 *                 "            <th>序号</th>\n" +
 *                 "\t\t\t<th>班级</th>\n" +
 *                 "\t\t\t<th>校区</th>\n" +
 *                 "\t\t\t<th>科目名称</th>\n" +
 *                 "\t\t\t<th>课程名称</th>\n" +
 *                 "\t\t\t<th>满班人数</th>\n" +
 *                 "\t\t\t<th>平均满班率(%)</th>\n" +
 *                 "\t\t</tr>\n" +
 *                 "\t</thead>\n" +
 *                 "\t\t<tr align=\"center\">\n" +
 *                 "            <td>1</td>\n" +
 *                 "            <td>1818888</td>\n" +
 *                 "            <td>华德公寓 Huade</td>\n" +
 *                 "            <td>听说能力 Speaking  Listening</td>\n" +
 *                 "            <td>SL-A1</td>\n" +
 *                 "            <td>5</td>\n" +
 *                 "            <td>0%</td>\n" +
 *                 "\t\t</tr>\n" +
 *                 "\t\t<tr align=\"center\">\n" +
 *                 "            <td>2</td>\n" +
 *                 "            <td>1809009</td>\n" +
 *                 "            <td>华德公寓 Huade</td>\n" +
 *                 "            <td>八岁爬藤课程 Young Learner</td>\n" +
 *                 "            <td>YL-STEAM</td>\n" +
 *                 "            <td>4</td>\n" +
 *                 "            <td>75%</td>\n" +
 *                 "\t\t</tr>\n" +
 *                 "\t\t<tr align=\"center\">\n" +
 *                 "            <td>3</td>\n" +
 *                 "            <td>1808015</td>\n" +
 *                 "            <td>华德公寓 Huade</td>\n" +
 *                 "            <td>八岁爬藤课程 Young Learner</td>\n" +
 *                 "            <td>YL1-T1</td>\n" +
 *                 "            <td>4</td>\n" +
 *                 "            <td>25%</td>\n" +
 *                 "\t\t</tr>\n" +
 *                 "\t\t<tr align=\"center\">\n" +
 *                 "            <td>4</td>\n" +
 *                 "            <td>1808016</td>\n" +
 *                 "            <td>华德公寓 Huade</td>\n" +
 *                 "            <td>八岁爬藤课程 Young Learner</td>\n" +
 *                 "            <td>YL1-T2</td>\n" +
 *                 "            <td>2</td>\n" +
 *                 "            <td>0%</td>\n" +
 *                 "\t\t</tr>\n" +
 *                 "\t\t<tr align=\"center\">\n" +
 *                 "            <td>5</td>\n" +
 *                 "            <td>1809011</td>\n" +
 *                 "            <td>上海校区 Shanghai</td>\n" +
 *                 "            <td>标化 Test Prep - TOEFL</td>\n" +
 *                 "            <td>W</td>\n" +
 *                 "            <td>6</td>\n" +
 *                 "            <td>0%</td>\n" +
 *                 "\t\t</tr>\n" +
 *                 "\t\t<tr align=\"center\">\n" +
 *                 "            <td>6</td>\n" +
 *                 "            <td>1809011</td>\n" +
 *                 "            <td>上海校区 Shanghai</td>\n" +
 *                 "            <td>标化 Test Prep - TOEFL</td>\n" +
 *                 "            <td>R</td>\n" +
 *                 "            <td>6</td>\n" +
 *                 "            <td>0%</td>\n" +
 *                 "\t\t</tr>\n" +
 *                 "\t\t<tr align=\"center\">\n" +
 *                 "            <td>7</td>\n" +
 *                 "            <td>1809011</td>\n" +
 *                 "            <td>上海校区 Shanghai</td>\n" +
 *                 "            <td>标化 Test Prep - TOEFL</td>\n" +
 *                 "            <td>S</td>\n" +
 *                 "            <td>6</td>\n" +
 *                 "            <td>0%</td>\n" +
 *                 "\t\t</tr>\n" +
 *                 "\t\t<tr align=\"center\">\n" +
 *                 "            <td>8</td>\n" +
 *                 "            <td>1809011</td>\n" +
 *                 "            <td>上海校区 Shanghai</td>\n" +
 *                 "            <td>标化 Test Prep - TOEFL</td>\n" +
 *                 "            <td>L</td>\n" +
 *                 "            <td>6</td>\n" +
 *                 "            <td>0%</td>\n" +
 *                 "\t\t</tr>\n" +
 *                 "\t\t<tr align=\"center\">\n" +
 *                 "            <td>9</td>\n" +
 *                 "            <td>1810001</td>\n" +
 *                 "            <td>上海校区 Shanghai</td>\n" +
 *                 "            <td>标化 Test Prep - TOEFL JUNIOR</td>\n" +
 *                 "            <td>小托福—语法</td>\n" +
 *                 "            <td>6</td>\n" +
 *                 "            <td>0%</td>\n" +
 *                 "\t\t</tr>\n" +
 *                 "\t\t<tr align=\"center\">\n" +
 *                 "            <td>10</td>\n" +
 *                 "            <td>1810001</td>\n" +
 *                 "            <td>上海校区 Shanghai</td>\n" +
 *                 "            <td>标化 Test Prep - TOEFL JUNIOR</td>\n" +
 *                 "            <td>小托福—听力</td>\n" +
 *                 "            <td>6</td>\n" +
 *                 "            <td>0%</td>\n" +
 *                 "\t\t</tr>\n" +
 *                 "\t\t<tr align=\"center\">\n" +
 *                 "            <td>11</td>\n" +
 *                 "            <td>1810001</td>\n" +
 *                 "            <td>上海校区 Shanghai</td>\n" +
 *                 "            <td>标化 Test Prep - TOEFL JUNIOR</td>\n" +
 *                 "            <td>小托福—阅读</td>\n" +
 *                 "            <td>6</td>\n" +
 *                 "            <td>0%</td>\n" +
 *                 "\t\t</tr>\n" +
 *                 "\t\t<tr align=\"center\">\n" +
 *                 "            <td>12</td>\n" +
 *                 "            <td>1808006</td>\n" +
 *                 "            <td>顺义校区 Shunyi</td>\n" +
 *                 "            <td>八岁爬藤课程 Young Learner</td>\n" +
 *                 "            <td>YL1-T2</td>\n" +
 *                 "            <td>4</td>\n" +
 *                 "            <td>0%</td>\n" +
 *                 "\t\t</tr>\n" +
 *                 "\t\t<tr align=\"center\">\n" +
 *                 "            <td>13</td>\n" +
 *                 "            <td>1808001</td>\n" +
 *                 "            <td>顺义校区 Shunyi</td>\n" +
 *                 "            <td>八岁爬藤课程 Young Learner</td>\n" +
 *                 "            <td>YL1-T2</td>\n" +
 *                 "            <td>4</td>\n" +
 *                 "            <td>0%</td>\n" +
 *                 "\t\t</tr>\n" +
 *                 "\t\t<tr align=\"center\">\n" +
 *                 "            <td>14</td>\n" +
 *                 "            <td>1808009</td>\n" +
 *                 "            <td>Ucharm</td>\n" +
 *                 "            <td>八岁爬藤课程 Young Learner</td>\n" +
 *                 "            <td>YL1-T2</td>\n" +
 *                 "            <td>3</td>\n" +
 *                 "            <td>0%</td>\n" +
 *                 "\t\t</tr>\n" +
 *                 "</table>\n" +
 *                 "</body>\n" +
 *                 "</html>";
 * <p>
 *     ExcelKit.createExcelFormTable("a",s,"xx.xls", getResponse(),getRequest());
 */
public class ExcelKit {


    /**
     * 把给出的html中的table导出为正确的excel格式导出
     *
     * @param sheetName excel的sheet名称
     * @param html      html字符串
     * @param filename  excel导出的文件名称 正确格式：名称.xls
     */
    public static void createExcelFormTable(String sheetName, String html, String filename, HttpServletResponse response, HttpServletRequest request) {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(sheetName);
        //CellStyle headStyle = createHeadStyle(wb);
        //CellStyle bodyStyle = createBodyStyle(wb);

        Document document = Jsoup.parse(html);

        Elements tables = document.getElementsByTag("table");
        Element element = tables.get(0);

        int index = 0;
        //body,第一个tr就是表头
        Elements tr = element.getElementsByTag("tr");
        for (Element trElem : tr) {
            HSSFRow rowtr = sheet.createRow(index);

            //表头
            Elements th = trElem.getElementsByTag("th");
            createCellAndSetValue(sheet, rowtr, th);

            //body主体部分
            Elements td = trElem.getElementsByTag("td");
            createCellAndSetValue(sheet, rowtr, td);
            index++;
        }

        writeToResponse(filename, response, request, wb);
    }

    private static void writeToResponse(String filename, HttpServletResponse response, HttpServletRequest request, HSSFWorkbook wb) {
        try {
            //清空一下response对象，否则出现缓存什么的
            response.reset();
            //指明这是一个下载的respond
            response.setContentType("application/x-download");
            //这里是提供给用户默认的文件名
            //String filename = "中文是没有问题的.xls";
            //双重解码、防止乱码
            filename = URLEncoder.encode(filename, "UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.addHeader("Content-Disposition", "attachment;" + FileKit.encodeFileName(request, filename));

            wb.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建单元格并且把值设置进去
     *
     * @param sheet HSSFSheet对象
     * @param rowtr table的行
     * @param td    table的列
     */
    private static void createCellAndSetValue(HSSFSheet sheet, HSSFRow rowtr, Elements td) {
        if (td != null && td.size() > 0) {
            for (int k = 0; k < td.size(); k++) {
                HSSFCell cell = rowtr.createCell(k);
                cell.setCellValue(td.get(k).text());
                sheet.autoSizeColumn(k, true);//单元格宽度自适应，不是很好
                sheet.setColumnWidth(k, sheet.getColumnWidth(k) * 13 / 10);//设置单元格快读是文字的1.3倍
            }
        }
    }


    /**
     * 设置表头样式
     *
     * @param wb Workbook对象
     */
    private static CellStyle createHeadStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        Font headerFont = wb.createFont();
        headerFont.setBold(true);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFont(headerFont);

        style.setBorderRight(BorderStyle.THICK);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(BorderStyle.THICK);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THICK);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THICK);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        return style;
    }

    /**
     * -
     * 设置表单记录样式
     *
     * @param wb Workbook对象
     */
    private static CellStyle createBodyStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        Font headerFont = wb.createFont();
        headerFont.setBold(true);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFont(headerFont);

        style.setBorderRight(BorderStyle.THICK);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(BorderStyle.THICK);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THICK);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THICK);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        return style;
    }
}
